# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html


class WeixinPipeline(object):
    query = 'select id,avatar,arcode from gzh where wxh=?'
    sql = 'insert into gzh(id, name, wxh, avatar, '\
                'arcode, `desc`, zhuti) values(?,?,?,?,?,?,?)'
    update_sql = 'update gzh set avatar=? where wxh=?'
    count = 0
    existed = 0
    update = 0
    id_ = 3681

    def open_spider(self, spider):      # get information of cur spider's customers and it's channel
        import sqlite3
        self.cnx = sqlite3.connect('weixin.db')
        self.cur = self.cnx.cursor()

    def process_item(self, item, spider):
        if item['name']:
            self.cur.execute(self.query, (item['wxh'], ))
            result = self.cur.fetchall()
            if result:
                print 'gzh %s(%s) existed~' % (item['name'], item['wxh'])
                sql = 'update gzh set %s="%s" where id=%s'
                if not result[0][1]:
                    self.cur.execute(sql % ('avatar', item['avatar'], result[0][0]))
                if not result[0][2]:
                    self.cur.execute(sql % ('arcode', item['qrcode'], result[0][0]))
                self.cnx.commit()
                self.existed += 1
                return
            self.id_ += 1
            self.cur.execute(self.sql, (self.id_, item['name'], item['wxh'], item['avatar'],
                    item['qrcode'], item['desc'], item['zhuti']))
            self.cnx.commit()
            self.count += 1
        else:
            self.cur.execute(self.update_sql, (item['avatar'], item['wxh']))
            self.update += 1
            self.cnx.commit()

    def close_spider(self, spider):
        self.cnx.close()
        txt = 'add new gzh %s\nscrap existed gzh %s\nupdate gzh %s' % (
            self.count, self.existed, self.update)
        print txt
